do "E:/ReplicateBuild/02_code/00_environment/00_set_environment.do"


clear

use "$basedata/estimationdata/All_T_J/All_T_J_choices_buff_0_0_lf", clear

bys itidx: egen any_next_school = max(next_school)
gen hired_left = (hired==1 & next_school!=1)
gen hired_declined = (hired==1 & any_next_school==1 & next_school!=1)
	
	summ hired_left if hired==1
local hired_left = 100*round(r(mean),.001)
summ hired_declined if hired==1
local hired_declined = 100*round(r(mean),.001)

egen first_day= min(applied_date), by(applicant_id app_year)
egen app_total=sum(1), by(applicant_id app_year)
egen first_day_apps=sum(1) if applied_date==first_day, by(applicant_id app_year)
gen first_day_share=first_day_apps/app_total
sum first_day_share
gen temp=(application_outcome_category!="neutral")
egen notes=max(temp), by(app_year job_id)
drop temp
egen pickone=tag (job_id app_year)
gen positive_eval=( application_outcome_category=="positive_assessment" | application_outcome_category=="interview" | application_outcome_category=="offer_declined")

egen hire_app_date_temp=mean(applied_date) if (application_outcome_category=="hired"), by(app_year job_id)
bys app_year job_id: egen hire_app_date = mean(hire_app_date_temp)
gen relative_date_app=applied_date-hire_app_date


egen interview_app_date_temp=mean(applied_date) if ( application_outcome_category=="hired" | application_outcome_category=="interview" | application_outcome_category=="offer_declined"), by(app_year job_id)
bys app_year job_id: egen interview_app_date = mean(interview_app_date_temp)
gen relative_date_app_interview=applied_date-interview_app_date

egen pos_app_date_temp=mean(applied_date) if (application_outcome_category=="positive_assessment" | application_outcome_category=="hired" | application_outcome_category=="interview" | application_outcome_category=="offer_declined"), by(app_year job_id)
bys app_year job_id: egen pos_app_date = mean(pos_app_date_temp)
gen relative_date_app_pos=applied_date-pos_app_date


gen app_outcome= application_outcome_category
replace app_outcome="interview" if application_outcome=="Recommend for Interview"
replace app_outcome="no_notes" if application_outcome=="neutral"
foreach i in hired interview middle_assessment negative_assessment positive_assessment neutral offer_declined withdrew {
    cap drop `i'
gen `i'=(application_outcome_category=="`i'")
}
preserve
replace hired = hired-hired_left
replace hired_left = hired_left-hired_declined
estpost tabstat hired hired_declined hired_left interview middle_assessment negative_assessment positive_assessment neutral offer_declined withdrew , stats(mean)
mat x=(e(hired), e(hired_declined), e(hired_left),  e(offer_declined), e(interview), e(positive_assessment), e(middle_assessment), e(negative_assessment),  e(withdrew), e(neutral))
mat rownames x="mean"
local numN = _N
foreach i in hired hired_declined hired_left interview middle_assessment negative_assessment positive_assessment neutral offer_declined withdrew {
replace `i'=`i'*`numN'
}
estpost tabstat hired hired_declined hired_left interview middle_assessment negative_assessment positive_assessment neutral offer_declined withdrew, stats(mean)
mat y=(e(hired), e(hired_declined), e(hired_left),  e(offer_declined), e(interview), e(positive_assessment), e(middle_assessment), e(negative_assessment),  e(withdrew), e(neutral))
mat rownames y="count"
matrix j=x\y
frmttable using "$output/tables/outcomes_application", statmat(j) replace va tex fra ///
	ctitles("","Hired successfully" "Hired but taught elsewhere", "Hired but not in district","Declined offer","Interview", "Positive", "Middle", "Negative","Withdrew", "No comment") ///
	sdec(5, 5, 5, 5, 5, 5,5, 5,5,5 \ 0,00, 0, 0, 0, 0, 0,0, 0   ) 
restore

preserve
egen non_offer_rating = rowtotal(interview middle_assessment negative_assessment positive_assessment withdrew)
collapse (max) hired interview middle_assessment negative_assessment positive_assessment neutral offer_declined withdrew non_offer_rating, by(job_id app_year)
summ non_offer_rating

estpost tabstat hired interview middle_assessment negative_assessment positive_assessment neutral offer_declined withdrew non_offer_rating, stats(mean)
mat x=(e(hired),  e(offer_declined), e(interview), e(positive_assessment), e(middle_assessment), e(negative_assessment),  e(withdrew), e(neutral), e(non_offer_rating))
mat rownames x="mean"
local numN = _N
foreach i in hired interview middle_assessment negative_assessment positive_assessment neutral offer_declined withdrew non_offer_rating {
replace `i'=`i'*`numN'
}
estpost tabstat hired interview middle_assessment negative_assessment positive_assessment neutral offer_declined withdrew non_offer_rating, stats(mean)
mat y=(e(hired),  e(offer_declined), e(interview), e(positive_assessment), e(middle_assessment), e(negative_assessment),  e(withdrew), e(neutral), e(non_offer_rating))
mat rownames y="count"
matrix j=x\y
frmttable using "$output/tables/outcomes_position", statmat(j) replace va tex fra ///
	ctitles("","Hired","Declined offer","Interview", "Positive", "Middle", "Negative","Withdrew", "No comment","Any Non-Hire Action") ///
	sdec(3, 3, 3, 3, 3, 3,3, 3,3 \ 0, 0, 0, 0, 0, 0,0,0, 0   ) 


restore



/* construct a table of summary statistics */

sum relative_date_app, d
mat a=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75) ,r(p90),r(sd))
mat rownames a = "All applications"
sum relative_date_app if neutral==1, d  
mat b=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames b = "No recorded evaluation"
sum relative_date_app if neutral!=1, d  
mat c=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames c = "Recorded evaluation"


matrix j=a\b\c

frmttable using "$output/tables/ref_date", statmat(j) replace va tex fra ///
	ctitles("","Obs","Mean","10th", "25th", "50th", "75th","90th", "Std. dev.") ///
	sdec(0, 1, 1, 1, 1, 1,1, 2 \ 0, 1, 1, 1, 1, 1,1, 2  \ 0, 1, 1, 1, 1, 1,1, 2  )
	
	
* time between application and posting date, separately by outcome

gen time_between_posting_and_app = applied_date-PostingDate

sum time_between_posting_and_app, d
mat a4=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75) ,r(p90),r(sd))
mat rownames a4 = "All applications"
sum time_between_posting_and_app if (application_outcome_category=="hired"), d  
mat b4=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames b4 = "Hired candidates"
sum time_between_posting_and_app if (application_outcome_category=="hired" | application_outcome_category=="interview" | application_outcome_category=="offer_declined"), d  
mat c4=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames c4 = "Interviewed candidates"
sum time_between_posting_and_app if (application_outcome_category=="positive_assessment" | application_outcome_category=="hired" | application_outcome_category=="interview" | application_outcome_category=="offer_declined"), d  
mat d4=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames d4 = "Positive assessment candidates"

matrix j4=a4\b4\c4\d4


frmttable using "$output/tables/app_time_since_posting", statmat(j4) replace va tex fra ///
	ctitles("","Obs","Mean","10th", "25th", "50th", "75th","90th", "Std. dev.") ///
	sdec(0, 1, 1, 1, 1, 1,1, 2  \ 0, 1, 1, 1, 1, 1,1, 2 \ 0, 1, 1, 1, 1, 1,1, 2 \ 0, 1, 1, 1, 1, 1,1, 2 )

* time from posting to interview, over the season

sum time_between_posting_and_app if (application_outcome_category=="hired" | application_outcome_category=="interview" | application_outcome_category=="offer_declined") & month(PostingDate)==4, d  
mat a5=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames a5 = "April"
sum time_between_posting_and_app if (application_outcome_category=="hired" | application_outcome_category=="interview" | application_outcome_category=="offer_declined") & (month(PostingDate)==5 | month(PostingDate)==6), d  
mat b5=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames b5 = "May/June"
sum time_between_posting_and_app if (application_outcome_category=="hired" | application_outcome_category=="interview" | application_outcome_category=="offer_declined") & (month(PostingDate)==7 | month(PostingDate)==8), d  
mat c5=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames c5 = "July/August"

matrix j5=a5\b5\c5

frmttable using "$output/tables/interview_time_since_posting_by_month", statmat(j5) replace va tex fra ///
	ctitles("","Obs","Mean","10th", "25th", "50th", "75th","90th", "Std. dev.") ///
	sdec(0, 1, 1, 1, 1, 1,1, 2 \ 0, 1, 1, 1, 1, 1,1, 2  \ 0, 1, 1, 1, 1, 1,1, 2  )
	
	

* calculate number of interviews per position
* calculate number of internal interviews per position
* calculate number of interviews per candidate
* calculate number of interviews per internal candidate

gen at_least_interview = (application_outcome_category=="hired" | application_outcome_category=="interview" | application_outcome_category=="offer_declined")
gen internal_interview = at_least_interview * current_lea
bys jtidx: egen num_interviews_j = sum(at_least_interview)
bys jtidx: egen num_internal_interviews_j = sum(internal_interview)

sum num_interviews_j if jtidx!=jtidx[_n-1], d  
mat a6=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames a6 = "Interviews per Position"
sum num_internal_interviews_j if jtidx!=jtidx[_n-1], d  
mat b6=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames b6 = "Internal Interviews per Position"

bys itidx: egen num_interviews_i = sum(at_least_interview)

sum num_interviews_i if itidx!=itidx[_n-1], d  
mat c6=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames c6 = "Interviews per Teacher"
sum num_interviews_i if itidx!=itidx[_n-1] & current_lea==1, d  
mat d6=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames d6 = "Interviews per Internal Teacher"
sum num_interviews_i if itidx!=itidx[_n-1] & num_interviews_i>=1, d  
mat e6=(r(N), r(mean),r(p10), r(p25) ,r(p50), r(p75), r(p90),r(sd))
mat rownames e6 = "Interviews per Teacher ($\ge$1)"

matrix j6=a6\b6\c6\d6\e6

frmttable using "$output/tables/interviews_distribution", statmat(j6) replace va tex fra ///
	ctitles("","Obs","Mean","10th", "25th", "50th", "75th","90th", "Std. dev.") ///
	sdec(0, 1, 1, 1, 1, 1,1, 2 \ 0, 1, 1, 1, 1, 1,1, 2  \ 0, 1, 1, 1, 1, 1,1, 2  \ 0, 1, 1, 1, 1, 1,1, 2  \ 0, 1, 1, 1, 1, 1,1, 2  )
	
gen inFOCAL = FOCALE!=.


forv yy=2011/2016 {
	summ inFOCAL if hired==1 & app_year==`yy'
	mat y`yy'=(r(N),r(mean))
	mat rownames y`yy' = "Hired Teachers, `yy'"

}
matrix j7=y2011\y2012\y2013\y2014\y2015\y2016
frmttable using "$output/tables/hired_teachers_match_rate", statmat(j7) replace va tex fra ///
	ctitles("","Obs","Match Rate") ///
	sdec(0, 2 \ 0,2\0,2\0,2\0,2\0,2)
	